Create Procedure [dbo].[asi_GetShippingMethodPriceList]
(
@ShipZone nvarchar(50),
@CalType int
)

AS

if   (@CalType = '3')   -- Of type Percentage Calculation type
    Begin
         
        select  a.ShipMethodKey, a.Name, a.VisibleName, a.Description, b.Charges ,  b.ShipZone, b.CalType from ShipMethod  a LEFT OUTER JOIN
        (select  sm.ShipMethodKey ShipMethodKey, 'United States' ShipZone, @CalType CalType, Charges =
                                            case
                                            when count(*) > 0 then 'Yes'
                                            else ''
                                        end
                                            from  ShipMethod sm where PercentOfOrder > 0
                  group by sm.ShipMethodKey  ) b
        ON a.ShipMethodKey = b.ShipMethodKey  
    end
else   
    begin
    select  a.ShipMethodKey, a.Name, a.VisibleName, a.Description, b.Charges ,  b.ShipZone, b.CalType from ShipMethod  a LEFT OUTER JOIN
    (select  sm.ShipMethodKey ShipMethodKey,  sm.Name Method,  sm.Description Description, sz.Name ShipZone,
                spr.ShipPriceCode CalType, Charges =
                                        case
                                        when count(*) > 0 then 'Yes'
                                        else ''
                                    end
                                        from  ShipPrice sp
                inner join ShipZone sz on sz.ShipZoneKey=sp.ShipZoneKey
                inner join ShipOrigin so on so.ShipOriginKey=sz.ShipOriginKey
                inner join ShipMethod sm on sp.ShipMethodKey=sm.ShipMethodKey
                inner join ShipPriceRef spr on sp.PricingTypeCode = spr.ShipPriceCode
                where    so.ShipOriginKey in ( select ParameterValue from SystemConfig where ParameterName='AddOn.Shipping.DefaultShipOriginKey')
                and sz.Name =  @ShipZone and spr.ShipPriceCode = @CalType
                group by sm.ShipMethodKey,  sm.Name, sm.Description, sm.Description , sz.Name, spr.ShipPriceCode) b
    ON a.ShipMethodKey = b.ShipMethodKey  
    end

GO